import org.apache.spark.sql.{SparkSession, DataFrame}
// 导入隐式转换
import spark.implicits._
object SparkSQLQuery {
  def main(args: Array[String]): Unit = {
    // 创建SparkSession
    val spark = SparkSession.builder()
      .appName("Spark SQL Query Example")
      .config("spark.master", "local")
      .getOrCreate()

    // 连接MySQL数据库
    val url = "jdbc:mysql://localhost:3306/sparksql_practice"
    val properties = new java.util.Properties()
    properties.setProperty("user", "lsz")
    properties.setProperty("password", "lsz")

    // 读取students表数据
    val studentsDF: DataFrame = spark.read.jdbc(url, "students", properties)

    // 查询所有年龄大于20岁的学生信息
    val ageGreaterThan20DF = studentsDF.filter($"age" > 20)
    println("所有年龄大于20岁的学生信息：")
    ageGreaterThan20DF.show()

    // 查找专业为“Computer Science”的学生，并按入学日期升序排序
    val computerScienceStudentsDF = studentsDF.filter($"major" === "Computer Science").orderBy($"enrollment_date".asc)
    println("专业为“Computer Science”的学生信息（按入学日期升序排序）：")
    computerScienceStudentsDF.show()

    // 统计各个专业的学生人数
    val studentsCountByMajorDF = studentsDF.groupBy($"major").count()
    println("各个专业的学生人数：")
    studentsCountByMajorDF.show()

    // 关闭SparkSession
    spark.stop()
  }
}

